#激活虚拟环境.\venv\Scripts\activate.ps1
#激活虚拟环境.\venv\Scripts\activate.ps1
from flask import Flask, render_template, request, redirect, url_for, jsonify
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import requests
import os
from werkzeug.utils import secure_filename  # 添加 secure_filename
from flask import send_file
from flask_migrate import Migrate
from openai import OpenAI
import shutil
import pytz
from dotenv import load_dotenv  # 新增导入

# 加载环境变量
load_dotenv()

# 在Flask应用初始化后添加配置
app = Flask(__name__)

# DeepSeek_V3 API配置（修改为从环境变量获取）
DEEPSEEK_API_KEY = os.getenv('DEEPSEEK_API_KEY')  # 必须配置
app.config['UPLOAD_FOLDER'] = os.path.join(os.getcwd(), 'uploads')  # 配置上传根目录
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 限制上传文件为16MB
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///audit_system.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///../instance/audit_system.db'
app.config['TIMEZONE'] = pytz.timezone('Asia/Shanghai')
db = SQLAlchemy(app)
migrate = Migrate(app, db)

# DeepSeek_V3 API配置
DEEPSEEK_API_URL = 'https://api.deepseek.com'

# 确保上传目录存在
os.makedirs(os.path.join(app.config['UPLOAD_FOLDER'], 'normative'), exist_ok=True)
os.makedirs(os.path.join(app.config['UPLOAD_FOLDER'], 'projects'), exist_ok=True)

# 数据库模型
class NormativeDocument(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    file_path = db.Column(db.String(256), nullable=False)
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)
    category = db.Column(db.String(64))  # 文件分类
    version = db.Column(db.String(32))   # 文件版本
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_normative_document_project_id'))

class ProjectDocument(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_project_document_project_id'), nullable=False)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    file_path = db.Column(db.String(256), nullable=False)
    document_type = db.Column(db.String(64))  # 例如：合同、设计文件等
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)

class InternalControlTest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # 显式指定外键约束名称
    project_id = db.Column(db.Integer, db.ForeignKey('project.id', name='fk_internal_control_test_project_id'), nullable=False)
    test_type = db.Column(db.String(64))  # 例如：符合性测试、实质性测试
    description = db.Column(db.Text, nullable=False)
    result = db.Column(db.Text)
    status = db.Column(db.String(32), default='Pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    status = db.Column(db.String(32), default='Pending')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # 建立反向关系，方便查询项目关联的文档和测试
    # 修改反向关系定义，添加级联删除
    normative_documents = db.relationship('NormativeDocument', backref='project', lazy=True, cascade="all, delete-orphan")
    project_documents = db.relationship('ProjectDocument', backref='project', lazy=True, cascade="all, delete-orphan")
    internal_control_tests = db.relationship('InternalControlTest', backref='project', lazy=True, cascade="all, delete-orphan")

# DeepSeek_V3辅助函数
def generate_internal_control_test(content):
    client = OpenAI(api_key=DEEPSEEK_API_KEY, base_url="https://api.deepseek.com")
    try:
        response = client.chat.completions.create(
    model="deepseek-chat",
    messages=[
        {"role": "system", "content": "你是一个专业的审计师，负责生成内部控制测评。"},
        {"role": "user", "content": f"根据以下内容生成内部控制测评：{content}"},
    ],
    stream=False
)
    # 修改为直接访问对象属性
        return response.choices[0].message.content
    except Exception as e:
        # 扩展异常处理范围
        return f"生成报告时发生错误: {str(e)}"

# 添加自定义模板过滤器
@app.template_filter('local_time')
def format_local_time(utc_time):
    if not utc_time:
        return ""
    return utc_time.replace(tzinfo=pytz.utc).astimezone(app.config['TIMEZONE']).strftime('%Y-%m-%d %H:%M:%S')

# 路由：首页
@app.route('/')
def index():
    projects = Project.query.all()
    return render_template('index.html', projects=projects)

# 路由：创建项目表单
@app.route('/projects/create-form')
def create_project_form():
    return render_template('projects/create.html')

# 路由：创建项目
@app.route('/projects/create', methods=['POST'])
def create_project():
    name = request.form.get('name', '').strip()
    description = request.form.get('description', '').strip()
    start_date_str = request.form.get('start_date')
    end_date_str = request.form.get('end_date')

    # 表单验证
    if not name:
        return render_template('projects/create.html', error='项目名称不能为空')
    if not start_date_str or not end_date_str:
        return render_template('projects/create.html', error='开始日期和结束日期不能为空')

    try:
        start_date = datetime.strptime(start_date_str, '%Y-%m-%d').date()
        end_date = datetime.strptime(end_date_str, '%Y-%m-%d').date()
    except ValueError:
        return render_template('projects/create.html', error='日期格式不正确，请使用 YYYY-MM-DD')

    project = Project(
        name=name,
        description=description,
        start_date=start_date,
        end_date=end_date,
        status='Active'
    )
    db.session.add(project)
    db.session.commit()

    return redirect(url_for('index'))

# 路由：上传规范性文件表单
@app.route('/normative-documents/upload-form')
# 新增：显示上传表单的路由（GET请求）
@app.route('/upload_normative_document/<int:project_id>/form', methods=['GET'])
def upload_normative_document_form(project_id):
    # 这里可以根据 project_id 做一些处理，比如查询项目信息
    # 例如：project = Project.query.get(project_id)
    return render_template('normative_documents/upload.html', project_id=project_id)

# 修改路由定义，添加 GET 方法支持
@app.route('/normative-documents/upload', methods=['GET', 'POST'])
def upload_normative_document():
    if request.method == 'GET':
        # 处理 GET 请求，例如显示上传表单
        return render_template('normative_documents/upload.html')
    elif request.method == 'POST':
        # 处理 POST 请求，例如处理文件上传
        project_id = request.form.get('project_id')
        name = request.form.get('name', '').strip()
        description = request.form.get('description', '').strip()
        category = request.form.get('category', '').strip()
        version = request.form.get('version', '').strip()
        file = request.files.get('file')

        # 表单验证
        if not name:
            return render_template('normative_documents/upload.html', error='文件名称不能为空')
        if not file:
            return render_template('normative_documents/upload.html', error='请上传文件')

        # 确保目录存在
        upload_dir = os.path.join(app.config['UPLOAD_FOLDER'], 'normative')
        os.makedirs(upload_dir, exist_ok=True)

        # 使用 secure_filename 处理文件名
        filename = secure_filename(file.filename)
        folder = os.path.join(app.config['UPLOAD_FOLDER'], 'normative')
        file_path = os.path.join(folder, file.filename)
        file.save(file_path)

        document = NormativeDocument(
            project_id=project_id,
            name=name,
            description=description,
            file_path=file_path,
            category=category,
            version=version
        )
        db.session.add(document)
        db.session.commit()

        return redirect(url_for('list_normative_documents', project_id=project_id))

# 路由：列出规范性文件
@app.route('/normative_documents/list/<int:project_id>')
def list_normative_documents(project_id):
    # 直接使用当前文件中定义的 NormativeDocument 模型
    documents = NormativeDocument.query.filter_by(project_id=project_id).all()
    return render_template('normative_documents/list.html', documents=documents, project_id=project_id)

# 路由：上传项目资料表单
@app.route('/projects/<int:project_id>/documents/upload-form')
def upload_project_document_form(project_id):
    return render_template('project_documents/upload.html', project_id=project_id)

# 路由：上传项目资料（修改文件保存逻辑）
@app.route('/projects/<int:project_id>/documents/upload', methods=['POST'])
def upload_project_document(project_id):
    # 添加项目存在性校验
    project = Project.query.get_or_404(project_id)
    
    name = request.form.get('name', '').strip()
    description = request.form.get('description', '').strip()
    document_type = request.form.get('document_type', '').strip()
    file = request.files.get('file')

    # 表单验证
    if not name:
        return render_template('project_documents/upload.html', project_id=project_id, error='文件名称不能为空')
    if not file:
        return render_template('project_documents/upload.html', project_id=project_id, error='请上传文件')

    # 确保目录存在
    upload_dir = os.path.join(app.config['UPLOAD_FOLDER'], 'projects', str(project_id))
    os.makedirs(upload_dir, exist_ok=True)

    # 使用 secure_filename 处理文件名
    filename = secure_filename(file.filename)
    # 原代码：file_path = f"uploads/projects/{project_id}/{file.filename}"
    # 修改为：
    # 修改文件保存路径生成方式
    folder = os.path.join(app.config['UPLOAD_FOLDER'], 'projects', str(project_id))  # 确保转为字符串
    os.makedirs(folder, exist_ok=True)
    file_path = os.path.join(folder, secure_filename(file.filename))  # 使用安全文件名
    
    file.save(file_path)

    document = ProjectDocument(
        project_id=project_id,
        name=name,
        description=description,
        document_type=document_type,
        file_path=file_path
    )
    db.session.add(document)
    db.session.commit()

    return redirect(url_for('project_documents', project_id=project_id))

# 路由：列出项目资料
@app.route('/projects/<int:project_id>/documents')
def project_documents(project_id):
    documents = ProjectDocument.query.filter_by(project_id=project_id).all()
    return render_template('project_documents/list.html', documents=documents, project_id=project_id)

# 路由：创建内部控制测试表单
@app.route('/projects/<int:project_id>/internal-controls/create-form')
def create_internal_control_test_form(project_id):
    return render_template('internal_controls/create.html', project_id=project_id)

# 路由：创建内部控制测试
@app.route('/projects/<int:project_id>/internal-controls/create', methods=['POST'])
def create_internal_control_test(project_id):
    test_type = request.form.get('test_type', '').strip()
    description = request.form.get('description', '').strip()
    result = request.form.get('result', '').strip()

    # 表单验证
    if not test_type:
        return render_template('internal_controls/create.html', project_id=project_id, error='测试类型不能为空')
    if not description:
        return render_template('internal_controls/create.html', project_id=project_id, error='描述不能为空')

    test = InternalControlTest(
        project_id=project_id,
        test_type=test_type,
        description=description,
        result=result,
        status='Completed'
    )
    db.session.add(test)
    db.session.commit()

    return redirect(url_for('project_internal_controls', project_id=project_id))

# 路由：列出内部控制测试
@app.route('/projects/<int:project_id>/internal-controls')
def project_internal_controls(project_id):
    tests = InternalControlTest.query.filter_by(project_id=project_id).all()
    return render_template('internal_controls/list.html', tests=tests, project_id=project_id)

# 路由：生成内部控制测评报告
@app.route('/projects/<int:project_id>/internal-controls/generate-report', methods=['POST'])
def generate_internal_control_report(project_id):
    # 获取项目相关资料
    documents = ProjectDocument.query.filter_by(project_id=project_id).all()
    tests = InternalControlTest.query.filter_by(project_id=project_id).all()

    # 整理资料内容
    document_content = "\n".join([f"{doc.name}: {doc.description}" for doc in documents])
    test_content = "\n".join([f"{test.test_type}: {test.description} - {test.result}" for test in tests])

    # 生成测评报告
    content = f"项目资料:\n{document_content}\n\n测试结果:\n{test_content}"
    report = generate_internal_control_test(content)

    return render_template('internal_controls/report.html', report=report, project_id=project_id)

@app.route('/delete_project/<int:project_id>')
def delete_project(project_id):
    project = Project.query.get_or_404(project_id)
    
    try:
        # 删除项目关联的规范性文件
        for norm_doc in project.normative_documents:
            # 删除物理文件
            if os.path.exists(norm_doc.file_path):
                os.remove(norm_doc.file_path)
                
            # 删除规范性文件项目专属目录（如果存在）
            normative_project_dir = os.path.join(
                app.config['UPLOAD_FOLDER'],
                'normative',
                f'project_{project_id}'  # 假设目录命名规则为project_+项目ID
            )
            if os.path.exists(normative_project_dir):
                shutil.rmtree(normative_project_dir)
        
        # 删除项目文档专属目录（保留原有逻辑）
        project_dir = os.path.join(
            app.config['UPLOAD_FOLDER'],
            'projects',
            str(project_id)
        )
        if os.path.exists(project_dir):
            shutil.rmtree(project_dir)
            
    except Exception as e:
        app.logger.error(f"文件删除失败: {str(e)}")

    # 删除数据库记录（自动级联删除关联记录）
    db.session.delete(project)
    db.session.commit()
    
    return redirect(url_for('index'))

@app.route('/download/<path:file_path>')
def download_file(file_path):
    try:
        return send_file(file_path, as_attachment=True)
    except Exception as e:
        return str(e), 404

if __name__ == '__main__':
    # 创建上传目录（在应用启动时）
    for folder in ['normative', 'projects']:
        path = os.path.join(app.config['UPLOAD_FOLDER'], folder)
        os.makedirs(path, exist_ok=True)  # exist_ok=True 防止目录已存在时报错
    
    with app.app_context():
        db.create_all()
    app.run(debug=True)